//
//  AlarmDataBase.m
//  SIPphone
//
//  Created by wrt on 14-2-14.
//  Copyright (c) 2014年 wrtsoft. All rights reserved.
//

#import "AlarmDataBase.h"

//数据库对象的单例
static AlarmDataBase *shareAlarmDataBase = nil;


@implementation AlarmDataBase
//单例的初始化方法
+ (AlarmDataBase *)shareAlarmDataBase
{
    @synchronized(self)
    {
        if (shareAlarmDataBase == nil)
        {
            shareAlarmDataBase = [[self alloc] init];
        }
    }
    return shareAlarmDataBase;
}
//创建数据库
- (void)creatDB
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //创建表
        char *error;
        NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ TEXT,%@ INTEGER,%@ INTEGER,%@ TEXT,%@ TEXT)",self.tableNameForUser,ALARM_ALARMURI,ALARM_PUBLISHURI,ALARM_STARTTIME,ALARM_ENDTIME,ALARM_RESULT,ALARM_REMARK,ALARM_TOMANAGER,ALARM_TYPE,ALARM_STATUS,ALARM_RECEIVETIME];
        //运行SQL语句
        if(sqlite3_exec(db, [createSQL UTF8String], NULL, NULL, &error) !=SQLITE_OK
           )
        {
            NSLog(@"建表失败！%s",error);
        }
        //关闭数据库
        sqlite3_close(db);
    }
}

//切换表名
- (void)changeTableName:(NSString *)userName
{
    self.tableNameForUser = [[NSString alloc] init];
    self.tableNameForUser = [NSString stringWithFormat:@"postmsg_%@",userName];
}


//读取数据库
- (void)readDB
{
    //将所需要存数据的数组初始化
    self.alarmUriArray = [[NSMutableArray alloc] init];
    self.publishUriArray = [[NSMutableArray alloc] init];
    self.startTimeArray = [[NSMutableArray alloc] init];
    self.endTimeArray = [[NSMutableArray alloc] init];
    self.resultArray = [[NSMutableArray alloc] init];
    self.remarkArray = [[NSMutableArray alloc] init];
    self.tomanagerArray = [[NSMutableArray alloc] init];
    self.alarmTypeArray = [[NSMutableArray alloc] init];
    self.alarmStatusArray = [[NSMutableArray alloc] init];
    self.alarmReceiveTimeArray = [[NSMutableArray alloc] init];
    
    //数据库先执行一遍读
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT %@,%@,%@,%@,%@,%@,%@,%@,%@,%@ FROM %@ ORDER BY %@ ",ALARM_ALARMURI,ALARM_PUBLISHURI,ALARM_STARTTIME,ALARM_ENDTIME,ALARM_RESULT,ALARM_REMARK,ALARM_TOMANAGER,ALARM_TYPE,ALARM_STATUS,ALARM_RECEIVETIME,self.tableNameForUser,ALARM_STARTTIME];
        
        sqlite3_stmt *statement;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            //执行
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                char *text1 = (char *)sqlite3_column_text(statement, 0);
                self.tempAlarmUri = [[NSString alloc] initWithUTF8String:text1];
                [self.alarmUriArray addObject:self.tempAlarmUri];
                
                char *text2 = (char *)sqlite3_column_text(statement, 1);
                self.tempPublishUri = [[NSString alloc] initWithUTF8String:text2];
                [self.publishUriArray addObject:self.tempPublishUri];
                
                char *text3 = (char *)sqlite3_column_text(statement, 2);
                self.tempStartTime = [[NSString alloc] initWithUTF8String:text3];
                [self.startTimeArray addObject:self.tempStartTime];
                
                char *text4 = (char *)sqlite3_column_text(statement, 3);
                self.tempEndTime = [[NSString alloc] initWithUTF8String:text4];
                [self.endTimeArray addObject:self.tempEndTime];
                
                char *text5 = (char *)sqlite3_column_text(statement, 4);
                self.tempResult = [[NSString alloc] initWithUTF8String:text5];
                [self.resultArray addObject:self.tempResult];
                
                char *text6 = (char *)sqlite3_column_text(statement, 5);
                self.tempRemark = [[NSString alloc] initWithUTF8String:text6];
                [self.remarkArray addObject:self.tempRemark];
                
                int int7 = sqlite3_column_int(statement, 6);
                self.tempTomanager = int7;
                [self.tomanagerArray addObject:[NSNumber numberWithInt:self.tempTomanager]];
                
                int int8 = sqlite3_column_int(statement, 7);
                self.tempAlarmType = int8;
                [self.alarmTypeArray addObject:[NSNumber numberWithInt:self.tempAlarmType]];
                
                char *text9 = (char *)sqlite3_column_text(statement, 8);
                self.tempAlarmStatus = [[NSString alloc] initWithUTF8String:text9];
                [self.alarmStatusArray addObject:self.tempAlarmStatus];
                
                char *text10 = (char *)sqlite3_column_text(statement, 9);
                self.tempAlarmReceiveTime = [[NSString alloc] initWithUTF8String:text10];
                [self.alarmReceiveTimeArray addObject:self.tempAlarmReceiveTime];
                
            }
        }
        //释放状态资源
        sqlite3_finalize(statement);
        //关闭数据库
        sqlite3_close(db);
    }
    
}
//写数据库操作
- (void)writeIntoDatabaseWithAlarmUri:(NSString *)alarmUri andPublishUri:(NSString *)publishUri andStartTime:(NSString *)startTime andEndTime:(NSString *)endTime andResult:(NSString *)result andRemark:(NSString *)remark andTomanager:(int)tomanager andAlarmType:(int)alarmType
{
    
    //获取存储的时间
    //表格
    NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
    //设定时间格式
    [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    //用[NSDate date]可以获取系统当前时间
    NSString *currentDateStr = [dateFormatter stringFromDate:[NSDate date]];
    
    //数据库写操作
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@,%@,%@,%@,%@,%@,%@) VALUES(?,?,?,?,?,?,?,?,?,?)",self.tableNameForUser,ALARM_ALARMURI,ALARM_PUBLISHURI,ALARM_STARTTIME,ALARM_ENDTIME,ALARM_RESULT,ALARM_REMARK,ALARM_TOMANAGER,ALARM_TYPE,ALARM_STATUS,ALARM_RECEIVETIME];
        sqlite3_stmt *statment;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            //绑定参数
            sqlite3_bind_text(statment, 1, [alarmUri UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 2, [publishUri UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 3, [startTime UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 4, [endTime UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 5, [result UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 6, [remark UTF8String], -1, NULL);
            sqlite3_bind_int(statment, 7, tomanager);
            sqlite3_bind_int(statment, 8, alarmType);
            sqlite3_bind_text(statment, 9, [@"未读" UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 10, [currentDateStr UTF8String], -1, NULL);
            //执行插入
            if(sqlite3_step(statment) !=SQLITE_DONE)
            {
                NSLog(@"插入失败");
            }
        }
        
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    [self readDB];
}
//删除数据库全部内容
- (void)removeAlldata
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE 1 > 0",self.tableNameForUser];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}
//删除单个数据
- (void)deleteWithStartTime:(NSString *)StartTime andAlarmUri:(NSString *)alarmUri andAlarmtype:(int)alarmtype
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = '%@' AND %@ = '%@' AND %@ = %d",self.tableNameForUser,ALARM_STARTTIME,StartTime,ALARM_ALARMURI,alarmUri,ALARM_TYPE,alarmtype];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}
//修改数据库未读状态的方法
- (void)editStatusWithStartTime:(NSString *)StartTime andAlarmUri:(NSString *)alarmUri andAlarmtype:(int)alarmtype
{
    
    //数据库执行修改
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *tempTime = [[NSString alloc] init];
        tempTime = StartTime;
        NSString *tempUri = [[NSString alloc] init];
        tempUri = alarmUri;
        int tempAlarmType = alarmtype;
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@' AND %@ = '%@' AND %@ = %d",self.tableNameForUser,ALARM_STATUS,@"已读",ALARM_STARTTIME,tempTime,ALARM_ALARMURI,tempUri,ALARM_TYPE,tempAlarmType];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
    [self readDB];
}
//获取未读的条数
- (int)countForUnread
{
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    
    int i = 0;
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ WHERE %@ = '%@'",self.tableNameForUser,ALARM_STATUS,@"未读"];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statment) == SQLITE_ROW)
            {
                i = sqlite3_column_int(statment, 0);
            }
        }
        //释放状态资源
        sqlite3_finalize(statment);
        //关闭数据库
        sqlite3_close(db);
    }
    return i;
}
//获取部分信息15一组
- (void)readDBForpartWithBeginNumber:(int)begin
{
    self.partAlarmUriArray = [[NSMutableArray alloc] init];
    self.partPublishUriArray = [[NSMutableArray alloc] init];
    self.partStartTimeArray = [[NSMutableArray alloc] init];
    self.partEndTimeArray = [[NSMutableArray alloc] init];
    self.partResultArray = [[NSMutableArray alloc] init];
    self.partRemarkArray = [[NSMutableArray alloc] init];
    self.partTomanagerArray = [[NSMutableArray alloc] init];
    self.partAlarmTypeArray = [[NSMutableArray alloc] init];
    self.partAlarmStatusArray = [[NSMutableArray alloc] init];
    self.partAlarmReceiveTimeArray = [[NSMutableArray alloc] init];
    
    //数据库先执行一遍读
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"alarm.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT %@,%@,%@,%@,%@,%@,%@,%@,%@,%@ FROM %@ ORDER BY %@ desc limit %d,15",ALARM_ALARMURI,ALARM_PUBLISHURI,ALARM_STARTTIME,ALARM_ENDTIME,ALARM_RESULT,ALARM_REMARK,ALARM_TOMANAGER,ALARM_TYPE,ALARM_STATUS,ALARM_RECEIVETIME,self.tableNameForUser,ALARM_STARTTIME,begin];
        
        sqlite3_stmt *statement;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            //执行
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                char *text1 = (char *)sqlite3_column_text(statement, 0);
                self.tempAlarmUri = [[NSString alloc] initWithUTF8String:text1];
                [self.partAlarmUriArray addObject:self.tempAlarmUri];
                
                char *text2 = (char *)sqlite3_column_text(statement, 1);
                self.tempPublishUri = [[NSString alloc] initWithUTF8String:text2];
                [self.partPublishUriArray addObject:self.tempPublishUri];
                
                char *text3 = (char *)sqlite3_column_text(statement, 2);
                self.tempStartTime = [[NSString alloc] initWithUTF8String:text3];
                [self.partStartTimeArray addObject:self.tempStartTime];
                
                char *text4 = (char *)sqlite3_column_text(statement, 3);
                self.tempEndTime = [[NSString alloc] initWithUTF8String:text4];
                [self.partEndTimeArray addObject:self.tempEndTime];
                
                char *text5 = (char *)sqlite3_column_text(statement, 4);
                self.tempResult = [[NSString alloc] initWithUTF8String:text5];
                [self.partResultArray addObject:self.tempResult];
                
                char *text6 = (char *)sqlite3_column_text(statement, 5);
                self.tempRemark = [[NSString alloc] initWithUTF8String:text6];
                [self.partRemarkArray addObject:self.tempRemark];
                
                int int7 = sqlite3_column_int(statement, 6);
                self.tempTomanager = int7;
                [self.partTomanagerArray addObject:[NSNumber numberWithInt:self.tempTomanager]];
                
                int int8 = sqlite3_column_int(statement, 7);
                self.tempAlarmType = int8;
                [self.partAlarmTypeArray addObject:[NSNumber numberWithInt:self.tempAlarmType]];
                
                char *text9 = (char *)sqlite3_column_text(statement, 8);
                self.tempAlarmStatus = [[NSString alloc] initWithUTF8String:text9];
                [self.partAlarmStatusArray addObject:self.tempAlarmStatus];
                
                char *text10 = (char *)sqlite3_column_text(statement, 9);
                self.tempAlarmReceiveTime = [[NSString alloc] initWithUTF8String:text10];
                [self.partAlarmReceiveTimeArray addObject:self.tempAlarmReceiveTime];
                
            }
            //释放状态资源
            sqlite3_finalize(statement);
            //关闭数据库
            sqlite3_close(db);
        }
    }
}


@end
